#!/bin/bash

APP=edu

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi

dim_user_zip="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dim_user_zip partition(dt)
select
    cast(id as string) id,
    login_name,
    nick_name,
    passwd,
    real_name,
    phone_num,
    email,
    user_level,
    birthday,
    gender,
    create_time,
    operate_time,
    start_date,
    if(rn=2,date_sub('$do_date',1),end_date) end_date,
    if(rn=1,'9999-12-31',date_sub('$do_date',1)) dt
from
(
    select
        id,
        login_name,
        nick_name,
        passwd,
        real_name,
        phone_num,
        email,
        user_level,
        birthday,
        gender,
        create_time,
        operate_time,
        start_date,
        end_date,
        row_number() over (partition by id order by start_date desc) rn
    from
    (
        select
            id,
            login_name,
            nick_name,
            passwd,
            real_name,
            phone_num,
            email,
            user_level,
            birthday,
            gender,
            create_time,
            operate_time,
            start_date,
            end_date
        from ${APP}.dim_user_zip
        where dt='9999-12-31'
        union
        select
            cast(id as string) id,
            login_name,
            nick_name,
            passwd,
            md5(real_name) name,
            md5(if(phone_num regexp '^(13[0-9]|14[01456879]|15[0-35-9]|16[2567]|17[0-8]|18[0-9]|19[0-35-9])\\\d{8}$',phone_num,null)) phone_num,
            md5(if(email regexp '^[a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\\\.[a-zA-Z0-9_-]+)+$',email,null)) email,
            user_level,
            birthday,
            gender,
            create_time,
            operate_time,
            '$do_date' start_date,
            '9999-12-31' end_date
        from
        (
            select
                data.id,
                data.login_name,
                data.nick_name,
                data.passwd,
                data.real_name,
                data.phone_num,
                data.email,
                data.user_level,
                data.birthday,
                data.gender,
                data.create_time,
                data.operate_time,
                row_number() over (partition by data.id order by ts desc) rn
            from ${APP}.ods_user_info_inc
            where dt='$do_date'
        )t1
        where rn=1
    )t2
)t3;
"

dim_course_info_full="
with
ci as
(
    select
        id,
        course_name,
        subject_id,
        chapter_num,
        publisher_id,
        teacher,
        origin_price,
        reduce_amount,
        actual_price,
        deleted,
        create_time,
        update_time
    from ${APP}.ods_course_info_full
    where dt='$do_date'
),
su as (
  select
        id,
        subject_name,
        category_id
    from ${APP}.ods_base_subject_info_full
    where dt='$do_date'
),
ct as (
  select
     id,
     category_name
    from ${APP}.ods_base_category_info_full
    where dt='$do_date'
)
insert overwrite table ${APP}.dim_course_info_full partition(dt='$do_date')
select
    ci.id,
    ci.course_name,
    ci.subject_id,
    su.subject_name,
    su.category_id,
    ct.category_name,
    ci.publisher_id,
    ci.teacher,
    ci.chapter_num,
    ci.origin_price,
    ci.reduce_amount,
    ci.actual_price,
    ci.deleted,
    ci.create_time,
    ci.update_time
from ci
    left join su on ci.subject_id = su.id
    left join ct on ct.id=su.category_id;
"

dim_paper_info_full="
insert overwrite table ${APP}.dim_paper_info_full partition (dt='$do_date')
select
    id,
    paper_title,
    course_id,
    deleted,
    create_time,
    update_time
from
(
    select
        cast(data.id as string) id,
        data.paper_title,
        cast(data.course_id as string) course_id,
        data.deleted,
        data.create_time,
        data.update_time
    from ${APP}.ods_test_paper_inc
    where dt='$do_date'
    and type='insert'
    union
    select
        id,
        paper_title,
        course_id,
        deleted,
        create_time,
        update_time
    from ${APP}.dim_paper_info_full
where dt=date_sub('$do_date',1)
) t1;
"

dim_province_full="
insert overwrite table ${APP}.dim_province_full partition(dt='$do_date')
select
    id,
    name,
    region_id,
    area_code,
    iso_code,
    iso_3166_2
from ${APP}.ods_base_province_full
where dt='$do_date';
"

dim_source_full="
insert overwrite table ${APP}.dim_source_full partition(dt='$do_date')
select
    id,
    source_site,
    source_url
from ${APP}.ods_base_source_full
where dt='$do_date';
"

case $1 in
"dim_user_zip")
    hive -e "$dim_user_zip"
;;
"dim_paper_info_full")
    hive -e "$dim_paper_info_full"
;;
"dim_course_info_full")
    hive -e "$dim_course_info_full"
;;
"dim_province_full")
    hive -e "$dim_province_full"
;;
"dim_source_full")
    hive -e "$dim_source_full"
;;
"all")
    hive -e "$dim_user_zip$dim_paper_info_full$dim_course_info_full$dim_province_full$dim_source_full"
;;
esac
